Date

Dates are values that represent a specific point in time, precise up to the second. They can also be referred to as datetime values. While dates are displayed as UTC (Coordinated Universal Time) or using the system's regional settings (see Default Format), in reality they are stored unformatted.

The Date property is stored in the Connect database with zero time zone offset, which makes it possible to convert the time correctly in any location. PReS Workflow, however, shows the date/time as it is stored in the database (with 0 time zone offset). This is expected behavior and the zone offset must be calculated manually in PReS Workflow.

Extracting dates

To extract data and have that data interpreted as a Date, set the type of the respective field to Date:

  1. Select the field in the data model.

  2. On the Step properties pane, under Field Definition, specify the Type as Date.

  3. Make sure that the date in the data source is formatted in a way that matches the expectations of the DataMapper. If the date doesn't match the format that the DataMapper expects, it cannot be interpreted as a date. For example, if a date in the data source is formatted as "yyyy-mm-dd" but the DataMapper expects a time as well, the date cannot be read and the DataMapper will stop with an error.
    The expected date format can be set in three places:

  4. For the letters and patterns that you can use in a date format, see Defining a date/time format.

    Data format settings tell the DataMapper how to read and parse data from the data source. They don't determine how these data are formatted in the Data Model or in a template. In the Data Model, data are converted to the native data type. Dates, for example, are converted to a DateTime object. How they are displayed in the Data Model depends on the preferences (see Default Format)..

Defining a date/time format

A date format is a mask representing the order and meaning of each digit in the raw data, as well as the date/time separators. The mask uses several predefined markers to parse the contents of the raw data. Here is a list of markers that are available in the DataMapper:

  • yy: Numeric representation of the Year when it is written out with only 2 digits (i.e. 13)

  • yyyy: Numeric representation of the Year when it is written out with 4 digits (i.e. 2013)

  • M: Short version of the month name ( i.e. Jan, Aug). These values are based on the current regional settings.

  • MM: Long version of the month name (i.e. January, August). These values are based on the current regional settings.

  • mm: Numeric representation of the month (i.e. 1, 09, 12)

  • D: Short version of the weekday name ( i.e. Mon, Wed). These values are based on the current regional settings.

  • DD: Long version of the weekday name (i.e. Monday, Wednesday). These values are based on the current regional settings.

  • dd: Numeric representation of the day of the month (i.e. 1, 09, 22)

  • hh: Numeric representation of the hours

  • nn: Numeric representation of the minutes

  • ss: Numeric representation of the seconds

  • ms: Numeric representation of the milliseconds.

  • ap: AM/PM string.

  • x: Indicates a time zone in the form of +05

  • xx: Indicates a time zone in the form of +0500

  • xxx: Indicates a time zone in the form of +05:00

  • In addition, any constant character can be included in the mask, usually to indicate date/time separators (i.e. / - :) . If one of those characters happens to be one of the reserved characters listed above, it must be escaped using the \ symbol.

The markers that can be used when extracting dates are different from those that are used to display dates in a template (see the Designer's Date and time patterns).

Examples of masks

Value in raw data​

Mask to use

June 25, 2013

MM dd, YYYY

06/25/13

​mm/dd/yy

​2013.06.25

yyyy.mm.dd

2013-06-25 07:31 PM

yyyy-mm-dd hh:nn ap

​2013-06-25 19:31:14.1206

yyyy-mm-dd hh:nn:ss.ms

Tuesday, June 25, 2013 @ 7h31PM

​DD, MM dd, yyyy @ hh\hnnap

Entering a date using JavaScript

In several places in the DataMapper, Date values can be set through a JavaScript. For example:

  • In a field in the Data Model. To do this, go to the Steps pane and select an Extract step. Then, on the Step properties pane, under Field Definition click the Add JavaScript Field button (next to the Field List drop-down). Type the JavaScript in the Expression field. (To rename the field, click the Order and rename fields button.)

  • In a Preprocessor property. To do this, go to the Steps pane and select the Preprocessor step. Then, on the Step properties pane, under Properties add a property, specify its Type as Date and put the JavaScript in the Default Value field.

The use of the JavaScript Date() object is necessary when creating dates through a JavaScript expression. For more information, see w3schools - JavaScript Dates and w3schools - Date Object.

Example

The following script creates a date that is the current date + 30 days:

function addDays(date, days) {
var result = new Date(date);
result.setDate(result.getDate() + days);
return result;
}
addDays(new Date(), 30);